package com.action.proceso6;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.bean.proceso4.UnidadMedida;
import com.bean.proceso6.Contrato;
import com.bean.proceso6.ItemOrden;
import com.conexion.DbConnection;
import com.mysql.jdbc.Connection;

public class ItemOrdenAction {

	private final String TABLA = "itemorden";

	public ItemOrden buscar(String codigo) {

		Connection conn = null;
		PreparedStatement pstm = null;
		ItemOrden oc = null;

		try {

			conn = DbConnection.getConnection();

			String sql = "Select * FROM " + TABLA + " where codigo=?";
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, codigo);

			ResultSet rs = pstm.executeQuery();

			if (rs.next())
				oc = new ItemOrden(rs.getString(1), rs.getInt(2),
						rs.getString(3), rs.getString(4), rs.getDouble(5));

		} catch (SQLException e) {
			Logger.getLogger(ItemOrdenAction.class.getName()).log(Level.SEVERE,
					null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return oc;
	}

	// para listar contratos

	public ArrayList<ItemOrden> listar() {

		Connection conn = null;
		PreparedStatement pstm = null;

		ArrayList<ItemOrden> lista = new ArrayList<ItemOrden>();
		try {
			conn = DbConnection.getConnection();

			String sql = "Select * FROM " + TABLA;
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();

			while (rs.next()) {
				lista.add(new ItemOrden(rs.getString(1), rs.getInt(2), rs
						.getString(3), rs.getString(4), rs.getDouble(5)));
			}

		} catch (SQLException e) {
			Logger.getLogger(ItemOrdenAction.class.getName()).log(Level.SEVERE,
					null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return lista;
	}

	// meto para listar contratos

	public ArrayList<ItemOrden> listarOC() {

		Connection conn = null;
		PreparedStatement pstm = null;

		ArrayList<ItemOrden> lista = new ArrayList<ItemOrden>();
		try {
			conn = DbConnection.getConnection();

			String sql = "Select * FROM " + TABLA + " where codigo like 'IC%'";
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();

			while (rs.next()) {
				lista.add(new ItemOrden(rs.getString(1), rs.getInt(2), rs
						.getString(3), rs.getString(4), rs.getDouble(5)));
			}

		} catch (SQLException e) {
			Logger.getLogger(ItemOrdenAction.class.getName()).log(Level.SEVERE,
					null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return lista;
	}

	public ArrayList<ItemOrden> listarOC(String criterio) {

		Connection conn = null;
		PreparedStatement pstm = null;

		ArrayList<ItemOrden> lista = new ArrayList<ItemOrden>();
		try {
			conn = DbConnection.getConnection();

			String sql = "Select i.* FROM itemorden i inner join unidadmedida u "
					+ "on i.unidadDeMedida_id=u.codigo where i.codigo like 'IC%' and  i.descripcion like ?";

			pstm = conn.prepareStatement(sql);
			pstm.setString(1, "%" + criterio + "%");

			ResultSet rs = pstm.executeQuery();

			while (rs.next()) {
				lista.add(new ItemOrden(rs.getString(1), rs.getInt(2), rs
						.getString(3), rs.getString(4), rs.getDouble(5)));
			}

		} catch (SQLException e) {
			Logger.getLogger(ItemOrdenAction.class.getName()).log(Level.SEVERE,
					null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return lista;
	}

	// meto para listar contratos

	public ArrayList<ItemOrden> listarOS() {

		Connection conn = null;
		PreparedStatement pstm = null;

		ArrayList<ItemOrden> lista = new ArrayList<ItemOrden>();
		try {
			conn = DbConnection.getConnection();

			String sql = "Select * FROM " + TABLA + " where codigo like 'IS%'";
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();

			while (rs.next()) {
				lista.add(new ItemOrden(rs.getString(1), rs.getInt(2), rs
						.getString(3), rs.getString(4), rs.getDouble(5)));
			}

		} catch (SQLException e) {
			Logger.getLogger(ItemOrdenAction.class.getName()).log(Level.SEVERE,
					null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return lista;
	}

	public int insertar(ItemOrden obj) {
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = DbConnection.getConnection();

			String sql = "INSERT INTO " + TABLA + " VALUES(?, ?, ?, ?, ?)";
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, obj.getCodigo());
			pstm.setInt(2, obj.getCantidad());
			pstm.setString(3, obj.getUnidadmedida());
			pstm.setString(4, obj.getDescripcion());
			pstm.setDouble(5, obj.getCosto());

			return pstm.executeUpdate();

		} catch (SQLException e) {
			Logger.getLogger(ContratoAction.class.getName()).log(Level.SEVERE,
					null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return -1;
	}

	public int eliminar(String codigo) {
		Connection conn = null;
		PreparedStatement pstm = null;
		try {

			conn = DbConnection.getConnection();

			String sql = "delete from " + TABLA + " where codigo=?";
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, codigo);

			return pstm.executeUpdate();

		} catch (SQLException e) {
			Logger.getLogger(OrdenDeCompraAction.class.getName()).log(
					Level.SEVERE, null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return -1;
	}

	public int actualizar(ItemOrden obj) {
		Connection conn = null;
		PreparedStatement pstm = null;

		try {

			conn = DbConnection.getConnection();

			String sql = "Update "
					+ TABLA
					+ " set cantidad=?, unidadDeMedida_id=?, descripcion=?, costo=? where codigo=?";
			pstm = conn.prepareStatement(sql);

			pstm.setInt(1, obj.getCantidad());
			pstm.setString(2, obj.getUnidadmedida());
			pstm.setString(3, obj.getDescripcion());
			pstm.setDouble(4, obj.getCosto());
			pstm.setString(5, obj.getCodigo());

			return pstm.executeUpdate();

		} catch (SQLException e) {
			Logger.getLogger(ContratoAction.class.getName()).log(Level.SEVERE,
					null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return -1;
	}

	public ArrayList<ItemOrden> listarOS(String criterio) {

		Connection conn = null;
		PreparedStatement pstm = null;

		ArrayList<ItemOrden> lista = new ArrayList<ItemOrden>();
		try {
			conn = DbConnection.getConnection();

			String sql = "Select i.* FROM itemorden i inner join unidadmedida u "
					+ "on i.unidadDeMedida_id=u.codigo where i.codigo like 'IS%' and i.descripcion like ?";

			pstm = conn.prepareStatement(sql);
			pstm.setString(1, "%" + criterio + "%");

			ResultSet rs = pstm.executeQuery();

			while (rs.next()) {
				lista.add(new ItemOrden(rs.getString(1), rs.getInt(2), rs
						.getString(3), rs.getString(4), rs.getDouble(5)));
			}

		} catch (SQLException e) {
			Logger.getLogger(ItemOrdenAction.class.getName()).log(Level.SEVERE,
					null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return lista;
	}

}
